-module(sql_storage).
-include("definitions.hrl").
-include("queries.hrl").

-export([
  start/1,
  stop/0,
  get_all_test_ids/0,
  get_test/1,
  add_test/1,
  insert_samples/0,
  get_state/1,
  get_last_state_for_device/1,
  update_state/1,
  init/1,
  get_last_state_for_devices/1]).

-define(CONNECTION_STRING, "DSN=odbcAsus;Provider=SQLNCLI11;Data Source=ASUS\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=DeviceTesting").


%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%интерфейс
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

-spec start/1 :: (pid()) -> ok.
start(ServerPid) ->
  register(sqlStorage, spawn(sql_storage, init, [ServerPid])),
  ok.


-spec init/1 :: (pid()) -> ok.
init(ServerPid) ->
  Conn = init_sql(),
  ServerPid ! {ok, storage_started},
  event_loop(Conn).

-spec init_sql/0 :: () -> pid().
init_sql() ->
  odbc:start(),
  X = odbc:connect(?CONNECTION_STRING, []),
  Conn = case X of
           {ok, Connection} ->
             {ok, IsDebug} = application:get_env(debug),
             case IsDebug of
               true ->
                 _ = odbc:sql_query(Connection, ?CLEAR_DB);
               _ -> ok
             end,
             Connection;
           _ -> error(no_connection)
         end,
  Conn.

-spec stop/0 :: () -> ok.
stop() ->
  try
    sqlStorage ! {stopSQl, self()},
    receive
      sqlStorage_stopped ->
        ok
    end
  catch
    error :_ ->
      ok
  end,
  ok.

-spec get_all_test_ids() -> [test_id()].
get_all_test_ids() ->
  sqlStorage ! {all, self()},
  receive
    {testIds, KeyList} -> KeyList
  end.

-spec get_test(test_id()) -> test().
get_test(TestId) ->
  sqlStorage ! {get, TestId, self()},
  receive
    {yourTest, Test} -> Test
  end.

-spec add_test(test()) -> test_id().
add_test(Test) ->
  sqlStorage ! {put, Test, self()},
  Rez =
    receive {nextId, Id} when is_integer(Id) -> Id
    end,
  Rez.

-spec insert_samples() -> ok.
insert_samples() ->
  InsLambda = fun(Test) ->
    add_test(Test)
  end,
  Samples = storage:get_samples(),
  lists:foreach(InsLambda, Samples).


-spec get_state/1 :: (device_id()) -> state().
get_state(StateId) ->
  sqlStorage ! {getState, StateId, self()},
  receive
    {your_state, State} -> State
  end.

-spec get_last_state_for_device/1 :: (device_id()) -> state().
get_last_state_for_device(DeviceId) ->
  sqlStorage ! {getLastForDevice, DeviceId, self()},
  receive
    {restored, State} ->
      State
  end.


-spec get_last_state_for_devices/1 :: (list(device_id())) -> list(state()).
get_last_state_for_devices(DeviceIdList) ->
  sqlStorage ! {getLastForDevices, DeviceIdList, self()},
  receive
    {restored_list, States} when is_list(States) -> States
  end.




-spec update_state/1 :: (state()) -> no_return().
update_state(State) ->
  sqlStorage ! {updateState, State, self()}.

-spec event_loop/1 :: (pid()) -> ok.
event_loop(Connection) ->
  receive

  %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  %% testCases messages
  %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
    {get, TestId, From} when is_pid(From), TestId > 0 ->
      get_test(TestId, From, Connection),
      event_loop(Connection);

    {put, Test, From} when is_pid(From) ->
      add_test(Test, From, Connection),
      event_loop(Connection);

    {all, From} when is_pid(From) ->
      get_all_tests_ids(From, Connection),
      event_loop(Connection);

  %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  %% stateRecords messages
  %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

    {updateState, State, From} when is_pid(From) ->
      update_state(Connection, State, From),
      event_loop(Connection);

    {getState, StateId, From} when is_pid(From), StateId > 0 ->
      get_state(Connection, StateId, From),
      event_loop(Connection);

    {getLastForDevice, DeviceId, From} when is_pid(From) ->
      Result = get_last_state_for_device(Connection, DeviceId),
      From ! {restored, Result},
      event_loop(Connection);

    {getLastForDevices, DeviceIdList, From} when is_pid(From), is_list(DeviceIdList) ->
      States = lists:map(
        fun(DeviceId) ->
          get_last_state_for_device(Connection, DeviceId)
        end,
        DeviceIdList),
      From ! {restored_list, States},
      event_loop(Connection);


%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%% stop
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
    {stopSQl, From} when is_pid(From) ->
      odbc:commit(Connection, commit),
      ok = odbc:disconnect(Connection),
      odbc:stop(),
      From ! sqlStorage_stopped,
      ok
  end.

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%реализация
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

-spec get_all_tests_ids/2 :: (pid(), pid()) -> {testIds, list(test_id())}.
get_all_tests_ids(From, Connection) ->
  {selected, _, Rows} = odbc:sql_query(Connection, ?GET_ALL_IDS),
  KeyList = parse_ids(Rows, []),
  From ! {testIds, KeyList}.

-spec get_test/3 :: (test_id(), pid(), pid()) -> {yourTest, test()}.
get_test(TestId, From, Connection) ->
  {selected, _, Rows} = odbc:param_query(Connection, ?GET_TEST_BY_ID,
    [{sql_integer, [TestId]}]),
  Input = get_input_test(TestId, Connection),
  Output = get_output_test(TestId, Connection),
  Result = parse_test(Rows, Input, Output),
  From ! {yourTest, Result}.


-spec add_test/3 :: (test(), pid(), pid()) -> {nextId, pos_integer()}.
add_test(Test, From, Connection) ->
  NextId = storage:get_next_id(),
  DBanswer = odbc:param_query(Connection, ?ADD_TEST,
    [
      {sql_integer, [NextId]},
      {sql_integer, [Test#testCase.deviceId]}
      , {{sql_varchar, 20}, [Test#testCase.description]}
    ]),
  case DBanswer of
    {error, Reason} -> io:format("Error : ~p~n", [Reason]);
    ok -> io:format(ok);
    {updated, 1} -> ok
  end,
  insert_messages(Connection, NextId, false, Test#testCase.dataField),
  insert_messages(Connection, NextId, true, Test#testCase.desiredAnswer),
  From ! {nextId, NextId}.

-spec get_state/3 :: (pid(), state_id(), pid()) -> {your_state, state()}.
get_state(Connection, StateId, From) ->
  {selected, _, Rows} = odbc:param_query(Connection, ?GET_STATE_BY_ID, [StateId]),
  State = parse_state(Rows, -1),
  Answers = get_answers_by_state(Connection, State),
  Result = State#stateRecord{answer = Answers},
  From ! {your_state, Result}.

-spec update_state/3 :: (pid(), state(), pid()) -> ok.
update_state(Connection, State, _) ->

  case get_sends(Connection, State) of
    -1 ->
      Params = get_state_tuple(State, true),
      odbc:param_query(Connection,
        ?CREATE_STATE,
        Params);
    SendsOld when is_integer(SendsOld) ->
      Params = get_state_tuple(State, false),
      Command = case State#stateRecord.completed of
                  true -> ?UPDATE_STATE_WITH_DATE;
                  _ -> ?UPDATE_STATE
                end,
      odbc:param_query(Connection, Command, Params),
      insert_answer(Connection, State, SendsOld)
  end,
  ok.

-spec get_last_state_for_device/2 :: (pid(), device_id()) -> state().
get_last_state_for_device(Connection, DeviceId) ->
  Params = [{sql_integer, [DeviceId]}],
  {selected, _, Rows} = odbc:param_query(Connection,
    ?GET_LAST_STATE_BY_DEVICE, Params),
  State = parse_state(Rows, DeviceId),
  Answers = get_answers_by_state(Connection, State),
  State#stateRecord{answer = Answers}.

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%хелперы для sql
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

-spec parse_ids/2 :: ([{}], [pos_integer()]) -> [pos_integer()].
parse_ids([], Acc) ->
  Acc;

parse_ids([Row | Rows], Acc) ->
  {Id} = Row,
  {IdInt, _} = string:to_integer(Id),
  parse_ids(Rows, [IdInt | Acc]).

-spec parse_test/3 :: ([{}], [message()], [message()]) -> test().
parse_test([], _, _) ->
  error("no such test");

parse_test(Rows, Input, Output) when length(Input) > 0 ->
  {Id, DeviceCode, Description} = hd(Rows),
  {Device, _} = string:to_integer(DeviceCode),
  {IdInt, _} = string:to_integer(Id),
  Result = #testCase{
    id = IdInt,
    deviceId = Device,
    description = Description,
    dataField = Input,
    desiredAnswer = Output
  },
  Result.

-spec get_output_test/2 :: (test_id(), pid()) -> [<<_:64>>].
get_output_test(TestId, Connection) ->
  {selected, _, Rows} = odbc:param_query(Connection, ?GET_TEST_OUTPUT,
    [{sql_integer, [TestId]}]),
  parse_messages(Rows, []).

-spec get_input_test/2 :: (test_id(), pid()) -> [<<_:64>>].
get_input_test(TestId, Connection) ->
  {selected, _, Rows} = odbc:param_query(Connection, ?GET_TEST_INPUT,
    [{sql_integer, [TestId]}]),

  parse_messages(Rows, []).


-spec parse_messages/2 :: ([{_, integer(), integer(), integer(), integer(), integer(), integer(), _, _, _}], [<<_:64>>]) -> [<<_:64>>].
parse_messages([], Acc) -> Acc;

parse_messages([Row | Rows], Acc) ->
  % [id] ,[fh] ,[deviceCode] ,[operation] ,[data] ,[fk] ,[crc] ,[testId] ,[orderNumber], [out]
  {_, Fh, DeviceCode, Operation, Data, Fk, Crc, _, _, _} = Row,
  Message = <<Fh:2, DeviceCode:8, Operation:12, Data:32, Fk:2, Crc:8>>,
  parse_messages(Rows, [Message | Acc]).

-spec insert_messages/4 :: (pid(), pos_integer(), boolean(), [<<_:64>>]) -> ok.
insert_messages(Connection, TestId, Out, Messages) ->
  Params = get_message_tuple_for_insert(TestId, Out, Messages),
  Ans = odbc:param_query(Connection, ?ADD_MESSAGE, Params),
  case Ans of
    ok -> io:format(ok);
    {updated, _} -> ok;
    {error, R} -> io:format("insertMessages : error:  ~p~n", [R]),
      timer:sleep(500),
      Ans1 = odbc:param_query(Connection, "INSERT INTO [DeviceTesting].[dbo].[log] ([ququ]) VALUES (1)", Params),
      case Ans1 of
        ok -> io:format(ok);
        {updated, _} -> ok;
        {error, R} -> io:format("~nDouble fail : ~p~n", [R])
      end
  end,
  ok.


-spec parse_state/2 :: (term(), integer()) -> state().
parse_state([], DeviceId) ->
  #stateRecord{id = -1, completed = false, deviceId = DeviceId, fails = 0, sends = 0, answer = []};

parse_state([Row | _], DeviceId) ->
  %{Id, TestId, DeviceId, Completed, Sends, Fails, Start, End} = Row,
  {Id, TestId, DeviceId, Completed, Sends, Fails, _, _} = Row,
  ComplBool = case Completed of
                1 -> true;
                0 -> false
              end,

  State = #stateRecord{id = Id, testId = TestId, deviceId = DeviceId, completed = ComplBool, sends = Sends, fails = Fails},
  State.

-spec get_state_tuple/2 :: (state(), boolean()) -> any().
get_state_tuple(State, IsNew) ->
  Result = case IsNew of
             true ->
               [
                 {sql_integer, [State#stateRecord.id]},
                 {sql_integer, [State#stateRecord.testId]},
                 {sql_integer, [State#stateRecord.deviceId]},
                 {sql_bit, [State#stateRecord.completed]},
                 {sql_integer, [State#stateRecord.sends]},
                 {sql_integer, [State#stateRecord.fails]}
               ];
             false ->
               [
                 {sql_bit, [State#stateRecord.completed]},
                 {sql_integer, [State#stateRecord.sends]},
                 {sql_integer, [State#stateRecord.fails]},
                 {sql_integer, [State#stateRecord.id]}
               ]
           end,
  Result.

-spec get_sends/2 :: (pid(), state()) -> integer().
get_sends(Connection, State) ->
  {selected, _, Rows} = odbc:param_query(Connection, ?GET_SENDS,
    [
      {sql_integer, [State#stateRecord.id]}
    ]),

  {CountStr} = hd(Rows),
  CountStr
.

-spec insert_answer/3 :: (pid(), #stateRecord{}, integer()) -> no_return().
insert_answer(Connection, State, SendsOld) ->
  ToSend = lists:nthtail(SendsOld, State#stateRecord.answer),
  insert_answers(Connection, State, ToSend, SendsOld + 1).


-spec insert_answers/4 :: (pid(), state(), [message()], integer()) -> no_return().
insert_answers(Connection, State, ToSend, StartOrder) ->
  case length(ToSend) of
    0 -> ok;
    _ ->
      Params = get_answer_tuple_for_insert(State#stateRecord.id, ToSend, StartOrder),
      Ans = odbc:param_query(Connection, ?ADD_ANSWERS, Params),
      case Ans of
        {updated, _} -> ok;
        {error, R} -> io:format("insertAnswers: ~s", [R])
      end
  end.


-spec get_answer_tuple_for_insert/3 :: (pos_integer(), [binary()], integer()) -> term().
get_answer_tuple_for_insert(StateId, AnswersListBinary, StartOrder) ->
  Parse = fun(<<FhInt:2, DeviceInt:8, OpInt:12, DataInt:32, FkInt:2, CrcInt:8>>) ->
    Res = #message{fh = FhInt, deviceCode = DeviceInt, operation = OpInt, data = DataInt, fk = FkInt, crc = CrcInt},
    Res end,

  AnswersList = lists:map(Parse, AnswersListBinary),
  Count = length(AnswersList),
  StateIdList = lists:duplicate(Count, StateId),
  IdsList = lists:map(fun(_) -> storage:get_next_id() end, AnswersList),
  FhList = lists:map(fun(Mess) -> Mess#message.fh end, AnswersList),
  DevCodeList = lists:map(fun(Mess) -> Mess#message.deviceCode end, AnswersList),
  OperationList = lists:map(fun(Mess) -> Mess#message.operation end, AnswersList),
  DataList = lists:map(fun(Mess) -> Mess#message.data end, AnswersList),
  FkList = lists:map(fun(Mess) -> Mess#message.fk end, AnswersList),
  CrcList = lists:map(fun(Mess) -> Mess#message.crc end, AnswersList),
  OrderList = lists:seq(StartOrder, StartOrder + Count - 1),
  Result = [
    {sql_integer, StateIdList},
    {sql_integer, IdsList},
    {sql_tinyint, FhList},
    {sql_tinyint, DevCodeList},
    {sql_integer, OperationList},
    {sql_integer, DataList},
    {sql_tinyint, FkList},
    {sql_integer, CrcList},
    {sql_integer, OrderList}
  ],

  Result.

%% -spec printList/1 :: ([term()]) -> no_return().
%% printList([]) ->
%%   io:format("empty~n");
%%
%% printList(List) ->
%%   lists:foreach(fun(El) -> io:format("~p  ", [El]) end, List),
%%   io:nl().

-spec get_message_tuple_for_insert/3 :: (pos_integer(), boolean(), [byte()]) -> [{atom(), [any()]}].
get_message_tuple_for_insert(TestId, Out, MessagesBin) ->
  Parse = fun(<<FhInt:2, DeviceInt:8, OpInt:12, DataInt:32, FkInt:2, CrcInt:8>>) ->
    Res = #message{fh = FhInt, deviceCode = DeviceInt,
      operation = OpInt, data = DataInt,
      fk = FkInt, crc = CrcInt},
    Res
  end,

  Messages = lists:map(Parse, MessagesBin),

  Count = erlang:length(Messages),
  IdsList = lists:map(fun(_) -> storage:get_next_id() end, Messages),
  FhList = lists:map(fun(Mess) -> Mess#message.fh end, Messages),
  DevCodeList = lists:map(fun(Mess) -> Mess#message.deviceCode end, Messages),
  OperationList = lists:map(fun(Mess) -> Mess#message.operation end, Messages),
  DataList = lists:map(fun(Mess) -> Mess#message.data end, Messages),
  FkList = lists:map(fun(Mess) -> Mess#message.fk end, Messages),
  CrcList = lists:map(fun(Mess) -> Mess#message.crc end, Messages),
  TestList = lists:duplicate(Count, TestId),
  OrderList = lists:seq(1, Count),
  OutList = case Out of
              false -> lists:duplicate(Count, 0);
              true -> lists:duplicate(Count, 1)
            end,

  Result = [
    {sql_integer, IdsList},
    {sql_tinyint, FhList},
    {sql_tinyint, DevCodeList},
    {sql_integer, OperationList},
    {sql_integer, DataList},
    {sql_tinyint, FkList},
    {sql_integer, CrcList},
    {sql_integer, TestList},
    {sql_integer, OrderList},
    {sql_integer, OutList}
  ],
  Result.

-spec get_answers_by_state/2 :: (pid(), state()) -> [<<_:64>>].
get_answers_by_state(Connection, State) ->
  {selected, _, Rows} = odbc:param_query(Connection, ?GET_ANSWERS_BY_STATE, [{sql_integer, [State#stateRecord.id]}]),
  parse_answers(Rows, []).

-spec parse_answers/2 :: ([{_, integer(), integer(), integer(), integer(), integer(), integer(), _, _, _}],
    [<<_:64>>]) -> [<<_:64>>].
parse_answers([], Acc) -> Acc;

parse_answers([Row | Rows], Acc) ->
  %{StateId, Id, Fh, DeviceCode, Operation, Data, Fk, Crc, OrderNumber} = Row,
  {_, _, Fh, DeviceCode, Operation, Data, Fk, Crc, _} = Row,
%%   {FhInt, _} = string:to_integer(Fh),
%%   {DeviceInt, _} = string:to_integer(DeviceCode),
%%   {OpInt, _} = string:to_integer(Operation),
%%   {DataInt, _} = string:to_integer(Data),
%%   {FkInt, _} = string:to_integer(Fk),
%%   {CrcInt, _} = string:to_integer(Crc),
%%   io:format("parseAnswers is int = ~p~n", [is_integer(CrcInt)]),
  Message = <<Fh:2, DeviceCode:8, Operation:12, Data:32, Fk:2, Crc:8>>,
  parse_messages(Rows, [Message | Acc]).
